import numpy as np
import pymysql
import pandas as pd
import pymysql.cursors

class MysqlUtils(object):
    """数据库工具类

    Args:
        object (_type_): _description_
    """
    
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            database='scenic',
            port=3306,
            charset='utf8'
        )
        
    def get_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT o.tourist_agency_name, u.id_no, LEFT(u.id_no, 2) as province_code, 
        DAYOFWEEK(gate.create_time) as non_weekend, cast(substring(u.id_no, 7, 4) as unsigned) as birth_year 
        FROM ticket_order_user_rel u JOIN ticket_order o on o.id = u.order_id JOIN order_user_gate_rel gate on gate.ticket_rel_id = u.id
        WHERE o.tourist_agency_name != '' and o.pay_time != ''
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        # print(df.head)
        
        # 数据处理
        df['non_weekend'] = df['non_weekend'].apply(lambda x : 1 if x not in [1, 7] else 0) # 非周末
        # 新增有效性标记列
        df['valid_id'] = df['id_no'].apply(lambda x: 1 if x and str(x).strip() != '' else 0)
        
        # 计算有记录的老年人及外省游客
        df['elderly_ratio'] = df.apply(
            lambda x : 1 if (x['valid_id'] and 2025 - x['birth_year'] >= 60) else 0 if x['valid_id'] else 0,
            axis=1
        )
        df['out_province_ratio'] = df.apply(
            lambda x : 1 if (x['valid_id'] and x['province_code'] != '44') else 0 if x['valid_id'] else 0,
            axis=1
        )
        # print(df.head)
        # 分组聚合计算占比
        result = df.groupby(['tourist_agency_name']).agg(
            total_visitors=('id_no', 'count'), # 总游客数
            valid_visitord=('valid_id', 'sum'), # 有效身份证游客数
            out_province=('out_province_ratio', 'sum'), # 外省人数（仅有效id_no）
            elderly=('elderly_ratio', 'sum'), # 老年人游客人数
            non_weekend_ratio=('non_weekend', 'mean'), # 分周末占比
        ).reset_index()
        
        # print(result.head)
        # 计算实际比例
        result['out_province_ratio'] = result['out_province'] / result['valid_visitord'].replace(0, np.nan)
        result['elderly_ratio'] = result['elderly'] / result['valid_visitord'].replace(0, np.nan)
        # 清除中间列
        result = result.drop(['out_province', 'elderly'], axis=1)
        result['out_province_ratio'] = result['out_province_ratio'].fillna(0)
        result['elderly_ratio'] = result['elderly_ratio'].fillna(0)
        
        result.to_csv('scenic_data.csv')
        
if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_data()



2.
import pandas as pd
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import KMeans
import matplotlib.pyplot as plt
from sklearn.metrics import silhouette_score

class ClusterUtils(object):
    
    def __init__(self):
        self.df = pd.read_csv('./scenic_data.csv')
        
    
    def get_k(self):
        # 提取特征并标准化
        features = self.df[['non_weekend_ratio','out_province_ratio','elderly_ratio']]
        scaler = StandardScaler()
        scaler_features = scaler.fit_transform(features)
        
        scores = []
        for k in range(2, 6):
            kmeans = KMeans(n_clusters=k, random_state=42)
            labels = kmeans.fit_predict(scaler_features)
            scores.append(silhouette_score(scaler_features, labels))
        
        plt.plot(range(2, 6), scores)
        plt.show()
        
if __name__ == '__main__':
    cu = ClusterUtils()
    cu.get_k()


3.
import pandas as pd
from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler
import matplotlib.pyplot as plt
import seaborn as sns
import warnings
warnings.filterwarnings('ignore')

class ClusterUtils(object):
    def __init__(self):
        self.df = pd.read_csv('./scenic_data.csv')
        
    def get_cluster(self):
        
        # 提取特征并标准化
        features = self.df[['non_weekend_ratio','out_province_ratio','elderly_ratio']]
        scaler = StandardScaler()
        scaler_features = scaler.fit_transform(features)
        
        # 使用k-means聚类
        kmeans = KMeans(n_clusters=4, random_state=42)
        self.df['cluster'] = kmeans.fit_predict(scaler_features)
        
        # 查看聚类结果
        print(self.df[['tourist_agency_name', 'cluster']])
        
        # 查看聚类中心（反标准化）
        centers = scaler.inverse_transform(kmeans.cluster_centers_)
        print(pd.DataFrame(centers, columns=['non_weekend_ratio','out_province_ratio','elderly_ratio']))
        
        # 保存结果
        self.df.to_csv('clustered_agencies.csv', index=False)
        # 将聚类中心转化为DataFrame
        centers_df = pd.DataFrame(centers, columns=features.columns)
        centers_df['cluster'] = [f'Cluster {i}' for i in range(centers.shape[0])]
        
        # 将宽边转换为长边（适合seaborn）
        centers_long = centers_df.melt(id_vars='cluster', var_name='feature', value_name='value')
        
        # 绘制分组柱状图
        # 设置字体
        plt.rcParams['font.sans-serif'] = ['SimHer']
        plt.rcParams['axes.unicode_minus'] = False
        colors = ['#1f77b4', '#ff7f0e', '#2ca02c', '#FFFF00'] # 蓝 橙 绿 黄
        plt.figure(figsize=(10, 6))
        sns.barplot(x='feature', y='value', hue='cluster', data=centers_long, palette=colors)
        plt.title('聚类中心特征对比')
        plt.ylabel('比例')
        plt.ylim(0, 1)
        plt.legend(title='簇', bbox_to_anchor=(1.05, 1), loc='upper left')
        
        # 添加数值标签
        for p in plt.gca().patches:
            height = p.get_height()
            plt.gca().text(
                p.get_x() + p.get_height() / 2, height + 0.02, f'{height: .2f}', ha='center'
            )
        plt.tight_layout()
        plt.show()


        
if __name__ == '__main__':
    cu = ClusterUtils()
    cu.get_cluster()